package com.szholly.pro.watergas_server.ps.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.pro.watergas_server.common.util.StationOnlineCompare;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.utils.session.SessionFactory;
import com.szholly.utils.session.provider.ISessionProvider;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.plug.safe.entity.user.UserEntity;

import lrq.common.MyLog;
import net.sf.json.JSONObject;

@Service
public class StationInfoService {
	/**
	 * 查询站点列表
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryStationList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		String sql = "select area.s_domaincaption area_name,city.s_domaincaption city_name,t2.device_num, t3.s_domaincaption, t4.river_basin_name, t5.river_name,"
				+ "  t.*,\n"
				+ "  t.code as objectid,\n"
				+ "'POINT('||t.x||' '||t.y||')' as wkt\n"
				+ " from BC_SITE_INFO t \n"
				+ " left join data_defulatdomain city on city.s_domainname = '行政区划' and city.s_domaincode = t.city_code"
				+"	left join data_defulatdomain area on area.s_domainname = '行政区划' and area.s_domaincode = t.area_code  "
				+ " left join BC_COLLECTION_INFO t2 on t.code=t2.site_id \n"		
				+ " left join data_defulatdomain t3 on t.station_category=t3.s_domaincode \n "
				+ " left join river_basin_info t4 on t.river_basin_code=t4.river_basin_code\n"
				+ " left join river_info t5 on t.river_code=t5.river_code "
				+ " where 1=1 "
				+ " and t3.s_domainname='监测类型' ";
		
		String userId = "-1";

		try {
			ISessionProvider session = SessionFactory.getSession();
			UserEntity userEntity = (UserEntity) session.getObject(ISessionProvider.UserEntity);
			userId = userEntity.getUserID();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		TwoTuple<String, List<GlobalParameter>> p = getStationQueryParams(params,false);

		if(!userId.equals("-1")){
			sql += " AND t.code in ( select station_code from OC_ROLE_SITE_PRIVILEGE t3"+
		               "     inner join safe_userrole t4 on t4.s_userid = @userid and t3.role_id = t4.s_roleid) ";
			p.second.add(new GlobalParameter("userid", userId));
		}
		
		String stationStatus = params.get("stationStatus");
		sql += p.first;
		if (params.containsKey("water_type")) {
			if(params.get("water_type").equals("3")){
				sql +=" order by to_number(t.code) asc";
			}else{
				sql +=" order by t.code asc";
			}
		}else{
			sql +=" order by t.code asc";
		}
		List<RowBase> rows;

		if (pageInfo == null) {
			rows = DataUtil.getSelectRows(sql, p.second, pTable);
		} else {
			rows = DataUtil.getPageRow(sql, p.second, pTable, pageInfo);
		}
		if(!StringUtil.IsNotNullOrEmpty(stationStatus)){
			rows = queryStationByStatus(rows, stationStatus);
		}
		Collections.sort(rows, new StationOnlineCompare());
	
		return rows;
	}
	/**
	 * 查询水质分析中通量分析中的贡献断面站点
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryContributeStationList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		String sql = "select  t2.device_num, t3.s_domaincaption, t4.river_basin_name, t5.river_name,"
				+ "  t.*,\n"
				+ "  t.code as objectid,\n"
				+ "'POINT('||t.x||' '||t.y||')' as wkt\n"
				+ " from BC_SITE_INFO t \n"
				+ " left join BC_COLLECTION_INFO t2 on t.code=t2.site_id \n"		
				+ " left join data_defulatdomain t3 on t.station_category=t3.s_domaincode \n "
				+" left join river_basin_info t4 on t.river_basin_code=t4.river_basin_code\n"
				+" left join river_info t5 on t.river_code=t5.river_code "
				+ " where 1=1 and t.build_status in ('0','-1') "
				+ " and t3.s_domainname='监测类型' "
				+" and t.station_category='1' "
				+ " and t.station_order<(select station_order from BC_SITE_INFO where code in ('"+params.get("station_id")+"')) "
				+" and t.river_section in ("+params.get("riverPart")+")";
		if (params.containsKey("riverCode")) {
			sql+="  and t.RIVER_CODE in ("+params.get("riverCode")+")"; 
		}
		if (params.containsKey("waterType")) {
			sql+="  and t.station_category in ("+params.get("waterType")+")"; 
		}
		sql +=" order by t.code";
		List<RowBase> rows;
		rows=DataUtil.getPageRow(sql, null, pTable, pageInfo);
		
		
		return rows;
	}
	/**
	 * 查询水质分析中通量分析中的贡献断面站点所处的污水站点
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> querySewageStationList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		String sql = "select  t2.device_num, t3.s_domaincaption, t4.river_basin_name, t5.river_name,"
				+ "  t.*,\n"
				+ "  t.code as objectid,\n"
				+ "'POINT('||t.x||' '||t.y||')' as wkt\n"
				+ " from BC_SITE_INFO t \n"
				+ " left join BC_COLLECTION_INFO t2 on t.code=t2.site_id \n"		
				+ " left join data_defulatdomain t3 on t.station_category=t3.s_domaincode \n "
				+" left join river_basin_info t4 on t.river_basin_code=t4.river_basin_code\n"
				+" left join river_info t5 on t.river_code=t5.river_code "
				+ " where 1=1 and t.build_status  in ('0','-1') "
				+ " and t3.s_domainname='监测类型' "
				+" and t.station_category='0' "
				+ " and t.station_order<(select station_order from BC_SITE_INFO where code='"+params.get("station_id")+"') "
				+" and t.river_section in ("+params.get("riverPart")+")";
		if (params.containsKey("riverCode")) {
			sql+="  and t.RIVER_CODE in ("+params.get("riverCode")+")"; 
		}
		if (params.containsKey("waterType")) {
			sql+="  and t.station_category in ("+params.get("waterType")+")"; 
		}
		sql +=" order by t.code";
		List<RowBase> rows;
		rows=DataUtil.getPageRow(sql, null, pTable, pageInfo);
		return rows;
	}
	/**
	 * 传输稳定性
	 * 每天晚上定时十一点半执行此方法
	 * @param params
	 * @throws Exception
	 */
	public void OnlineStatusStaticTrigger(String start,String end,String station_code) throws Exception{
		HashMap<String,String> paramss=new HashMap<>();
		HashMap<String,Object> entityData=new HashMap<>();		
		String WATER_TYPE="3";
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");	
		if(StringUtil.IsNotNullOrEmpty(WATER_TYPE)){
			paramss.put("WATER_TYPE", WATER_TYPE);
		}		
		if(StringUtil.IsNotNullOrEmpty(station_code)){
			paramss.put("station_code", station_code);
		}	
		try {
			DataUtil.beginTransaction();			
			clearAlarmDayInfo(start);			
			//获取所有站点
			List<RowBase> stationList=queryStationList(paramss, null, null);
			for(RowBase row:stationList){
				String stationId = row.getSafeString("CODE");
				
				List<RowBase> list=QueryAlarmInfo(start + " 00:00:00",end+" 23:59:59",stationId);
				int arpCount=0;//掉线次数
				int rangeCount=0;//每天允许掉线次数
				int CONTINUE_TIME=0;//掉线总时长
				if(list.size()>0){
					for(RowBase rowinfo:list){
						int currentContinueTime = (int) rowinfo.getSafeDouble("CONTINUE_TIME");
						//如果掉线时长大于五分钟，掉线次数统计一次，
						if(currentContinueTime>5){
							arpCount++;						
							CONTINUE_TIME=currentContinueTime+CONTINUE_TIME;
						}else{
							rangeCount++;
							//当掉线时长小于五分钟的次数累加大于5次，进行掉线次数累加
							if(rangeCount>5){
								CONTINUE_TIME=currentContinueTime+CONTINUE_TIME;
								arpCount++;
							}
						}
						
					}	
					if(arpCount>0){
						
						if(CONTINUE_TIME > (60*24)){
							CONTINUE_TIME = 60*24;
						}
						
						entityData.put("CONTINUE_TIME", CONTINUE_TIME);//每天掉线总时长
						entityData.put("ARPCOUNT", arpCount);//每天掉线总次数
						entityData.put("START_TIME", dateFormat.parseObject(start+" 00:00:00") );//开始时间
						entityData.put("RECOVER_TIME", dateFormat.parseObject(end+" 23:59:59"));//恢复时间
						entityData.put("TYPE", "4");//报警类型
						entityData.put("STATION_CODE",row.getSafeString("CODE"));//站点ID
						ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
						ITable pTable = tableFactory.openTable("operation_alarm_day_info");
						addOperationAlarmDayInfo(pTable.getTableName(), entityData);		
					}
				}
			}
			DataUtil.commitTransaction();
		} catch (Exception e) {
			DataUtil.rollbackTransaction();
			throw e;
		}
	}

	public void addOperationAlarmDayInfo(String tableName,HashMap<String, Object> params){
		DataUtil.Create(tableName, params);
	}
	
	/**
	 * 清除报警数据
	 * gislin
	 * 2016年8月12日
	 * @param date
	 */
	private void clearAlarmDayInfo(String date){
		String sql = " delete FROM operation_alarm_day_info WHERE TO_CHAR(START_TIME,'yyyy-mm-dd') = '"+date+"'";
		DataUtil.ExecuteUpdateSql(sql, null);
	}
	
	/**
	 * 获取故障站点
	 * @param params
	 * @return
	 */
	public List<RowBase> queryStationStafault(HashMap<String, String> params){
		String sql="select substr(t1.name,instr(t1.name,'、')+1,length(t1.name)-instr(t1.name,'、')) as NAME,"
				+ "t1.code,count(t4.s_domaincaption) as FAULTCount from BC_SITE_INFO t1,OP_ALAR_PROC t2,"
				+" operation_task t3,data_defulatdomain t4  where "
				+ " t4.s_domaincode=t2.exception_reason and t4.s_domainname='供水异常故障项' and t1.code=t3.station_id and t2.task_code=t3.task_code ";
		if(params.containsKey("station_id")) {
   			sql += " and t1.code='"+params.get("station_id")+"'";
   		}
		if(params.containsKey("start")){
			sql += " and t2.createtime >= to_date('" + params.get("start")
			+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("end")){
			sql += " and t2.end_time <= to_date('" + params.get("end")
			+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+=" group by t1.name,t1.code order by to_number(t1.code) asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 获取站点故障项
	 * @param params
	 * @return
	 */
	public List<RowBase> queryOperationAlarmProcess(HashMap<String, String> params){
		
		String sql="select ms.name,dd.s_domaincaption,count(dd.s_domaincaption) as FAULTCount,ms.code from OP_ALAR_PROC t"
       +" left join operation_task ot on t.task_code = ot.task_code "
       +" left join data_defulatdomain dd on dd.s_domaincode = t.exception_reason and dd.s_domainname = '供水异常故障项'"
       +" left join BC_SITE_INFO ms on ms.code = ot.station_id where 1=1 ";
		if(params.containsKey("station_id")) {
   			sql += " and ms.code='"+params.get("station_id")+"'";
   		}
		if(params.containsKey("start")){
			sql += " and t.createtime >= to_date('" + params.get("start")
			+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("end")){
			sql += " and t.end_time <= to_date('" + params.get("end")
			+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+=" group by ms.name, dd.s_domaincaption,ms.code";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	
	/**
	 * 站点状态统计
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public Map<String,Object> queryStationStatusCountListForYanShi(Map<String, String> params,PageInfo pageInfo) throws Exception{		
		int staNormal=0;//0正常
		int staOverproof=0;//1超标
		int staExecption=0;//2异常
		int staFault=0;//3故障
		Map<String,Object> mapstatus=new HashMap<>();
		String stationStatus = params.get("stationStatus");
		params.remove("stationStatus");
		List<RowBase> rowlist=queryStationList(params,null,pageInfo);//获取站点信息
		
		for(RowBase row:rowlist){
			String mn = row.getSafeString("DEVICE_NUM");
			String status = RedisHelper.getInstance().getStationStatus(mn);	
			
			if("1".equals(status)){
				staOverproof=staOverproof+1;
			}
			else{
				staNormal=staNormal+1;
				row.setValue("STATION_STATUS","0");
			}
		}
		
		rowlist = queryStationByStatus(rowlist, stationStatus);
		
		//演示用
		for(RowBase row : rowlist){
			row.setValue("STATION_STATUS", "0");
		}
		
		mapstatus.put("staFault",String.valueOf(staFault));
		mapstatus.put("staOverproof",String.valueOf(staOverproof));
		mapstatus.put("staNormal",String.valueOf(staNormal));
		mapstatus.put("staExecption",String.valueOf(staExecption));
		mapstatus.put("data",rowlist);

		return mapstatus;
	}
	/**
	 * 当前站点状态统计
	 * @param params
	 * @return
	 * @throws Exception 
	 */
	public Map<String,Object> StatictisStatus(Map<String, String> params,PageInfo pageInfo) throws Exception{
		int staNormal=0;//0正常
		int staOverproof=0;//1超标
		int staExecption=0;//2异常
		int staFault=0;//3故障
		Map<String,Object> mapstatus=new HashMap<>();
		params.remove("stationStatus");
		List<RowBase> rowlist=queryStationList(params,null,pageInfo);//获取站点信息		
		List<Object> list=new ArrayList<>(); 
		for(RowBase row:rowlist){
			Map<String,Object> stationlist=new HashMap<>();
			String currentstatus="";//存储站点当前状态
			String mn = row.getSafeString("DEVICE_NUM");
			String DEVICEstatus = RedisHelper.getInstance().getStationStatus(mn);	
			if(DEVICEstatus == null || DEVICEstatus.trim().equals("")){
				staFault=staFault+1;
				currentstatus="故障";
			}else{
				if(DEVICEstatus.equals("0")){//正常
					staNormal=staNormal+1;
					currentstatus="正常";
				}else if(DEVICEstatus.equals("1")){//超标
					staOverproof=staOverproof+1;
					currentstatus="超标";
				}else if(DEVICEstatus.equals("2")){//异常
					staExecption=staExecption+1;
					currentstatus="异常";
				}else if(DEVICEstatus.equals("3")){//故障
					staFault=staFault+1;
					currentstatus="故障";
				}
			}
			stationlist.put("CURRENTSTATUS", currentstatus);
			stationlist.put("stationlist", row);
			list.add(stationlist);
		}
		mapstatus.put("staFault",String.valueOf(staFault));
		mapstatus.put("staOverproof",String.valueOf(staOverproof));
		mapstatus.put("staNormal",String.valueOf(staNormal));
		mapstatus.put("staExecption",String.valueOf(staExecption));
		mapstatus.put("data",list);
		mapstatus.put("dataSize",list.size());
		return mapstatus;
	}
	
	/**
	 * 左边导航站点状态统计
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public Map<String,Object> queryStationStatusCountList(Map<String, String> params,PageInfo pageInfo) throws Exception{		
	
		int staNormal=0;//0正常
		int staOverproof=0;//1超标
		int staExecption=0;//2异常
		int staFault=0;//3故障
		Map<String,Object> mapstatus=new HashMap<>();
		try {
		String stationStatus = params.get("stationStatus");
	
		params.remove("stationStatus");
		List<RowBase> rowlist=queryStationList(params,null,pageInfo);
		for(RowBase row:rowlist){
			String mn = row.getSafeString("DEVICE_NUM");
			String status = RedisHelper.getInstance().getStationStatus(mn);	
			if(status == null || status.trim().equals("")){
				staFault=staFault+1;
			}else{
				if(status.equals("0")){//正常
					staNormal=staNormal+1;
				}else if(status.equals("1")){//超标
					staOverproof=staOverproof+1;
				}else if(status.equals("2")){//异常
					staExecption=staExecption+1;
				}else if(status.equals("3")){//故障
					staFault=staFault+1;
				}
			}
		}
		
		rowlist = queryStationByStatus(rowlist, stationStatus);
		
		mapstatus.put("staFault",String.valueOf(staFault));
		mapstatus.put("staOverproof",String.valueOf(staOverproof));
		mapstatus.put("staNormal",String.valueOf(staNormal));
		mapstatus.put("staExecption",String.valueOf(staExecption));
		mapstatus.put("data",rowlist);		
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mapstatus;
	}
	
	/**
	 * 根据站点状态过滤
	 * @param stationList
	 * @param stationStatus
	 * @return
	 */
	private List<RowBase> queryStationByStatus(List<RowBase> stationList,String stationStatus){
		List<RowBase> resultRows = new ArrayList<>();				
		for(RowBase row : stationList){
			String mn = row.getSafeString("DEVICE_NUM");		
			String status = RedisHelper.getInstance().getStationStatus(mn);	
			if(status == null || status.trim().equals("")){
				status = "3";
			}						
			row.setValue("STATION_STATUS",status);
			if(stationStatus != null && stationStatus.equals(status)){
				resultRows.add(row);
			}
			else if(stationStatus == null){
				resultRows.add(row);
			}
		}
		return resultRows;
	}
	
	private TwoTuple<String, List<GlobalParameter>> getStationQueryParams(Map<String, String> params,boolean isContribute) {
		StringBuilder sql = new StringBuilder();
		List<GlobalParameter> listParam = new ArrayList<>();

		if (params.containsKey("wwtp_id")) {
			sql.append(" and t.WWTP_ID=@wwtp_id");
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		
		if (params.containsKey("station_code")) {
			sql.append(" and t.code=@station_code");
			listParam.add(new GlobalParameter("station_code", params.get("station_code")));
		}
		if (params.containsKey("flow_type")) {
			sql.append(" and t.flow_type=@flow_type");
			listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
		}
		if (params.containsKey("station_id")) {
			sql.append(" and t.code=@station_id");
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("station_id_like")) {
			sql.append(" and t.code like '__" + params.get("station_id_like") + "%'");
		}
		if (params.containsKey("station_name")) {
			sql.append(" and t.name like '%" + params.get("station_name") + "%'");
		}
		
		if(params.containsKey("build_status")){
			String buildStatu = params.get("build_status").toString();
			String[] buildStatus = buildStatu.split(",");
			String buildStatuIn = lrq.common.StringUtil.getArrayStrForSql(buildStatus, "buildStatu");
			sql.append(" and t.build_status in "+buildStatuIn+" ");
			listParam.addAll(DataUtil.arrayToParameters(buildStatus, "buildStatu"));
		}
		else{
			sql.append(" and t.build_status = '0' ");
		}
		
		if(params.containsKey("riverPart")){
			String riverPart = params.get("riverPart").toString();
			String[] riverParts = riverPart.split(",");
			String riverPartIn = lrq.common.StringUtil.getArrayStrForSql(riverParts, "riverPart");
			sql.append(" and t.RIVER_SECTION in "+riverPartIn+" ");
			listParam.addAll(DataUtil.arrayToParameters(riverParts, "riverPart"));
		}
		if(params.containsKey("riverCode")){
			String riverCode = params.get("riverCode");
			sql.append(" and t.RIVER_CODE in (" + riverCode + ")");
			listParam.add(new GlobalParameter("riverCode", riverCode));
		}
		
		if(params.containsKey("stationLocation")) {
			String stationLocation = params.get("stationLocation");
			sql.append(" and t.station_location in ('" + stationLocation + "')");
			listParam.add(new GlobalParameter("stationLocation", stationLocation));
		}
		
		//查询多个类型的站点，包括本专题和污水处理厂的
		if(isContribute) {
			if(params.containsKey("waterType")){
				String waterType = params.get("waterType");
				sql.append(" and t.station_category in (" + waterType + ")");
				listParam.add(new GlobalParameter("waterType", waterType));
			}
		} else {
			if (params.containsKey("otherStation")) {
				
				if (params.containsKey("water_type")) {
					sql.append(" and t.station_category!=@water_type");
					listParam.add(new GlobalParameter("water_type", params.get("water_type")));
				}
			} else {
				if (params.containsKey("water_type")) {
					sql.append(" and t.station_category=@water_type");
					listParam.add(new GlobalParameter("water_type", params.get("water_type")));
				}
			}
		}
		
		if (params.containsKey("companyCode")) {
			sql.append(" and t.wwtp_id=@companyCode");
			listParam.add(new GlobalParameter("companyCode", params.get("companyCode")));
		}
		
		return new TwoTuple<String, List<GlobalParameter>>(sql.toString(), listParam);
	}
	
	/**
	 * 获取站点污染物
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public JSONObject queryStationPollutantList() throws Exception {
		String wwtp_id = RequestUnit.getString("WWTP_ID");
		String station_id = RequestUnit.getString("STATION_ID");
		String deviceId = RequestUnit.getString("deviceId");
		String waterType = RequestUnit.getString("WATER_TYPE");
		
		Map<String,String> params = new HashMap<>();
		if(StringUtil.IsNotNullOrEmpty(wwtp_id)){
			params.put("wwtp_id", wwtp_id);
		}
		if(StringUtil.IsNotNullOrEmpty(station_id)){
			params.put("station_id", station_id);
		}
		if(StringUtil.IsNotNullOrEmpty(deviceId)){
			params.put("deviceId", deviceId);
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			params.put("stationCategory", waterType);
		}
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("BC_SITE_POLL");
		
		PageInfo pageInfo = DataUtil.getPageInfo();
		
		String sql = " SELECT t1.*,t2.name as STATIONNAME from BC_SITE_POLL t1 "
				+ "	left join BC_SITE_INFO t2 on t1.SITE_ID=t2.exp_no " + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t2.wwtp_id=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t1.SITE_ID=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		if (params.containsKey("deviceId")) {
			sql += " and t1.DEVICE_ID=@deviceId";
			listParam.add(new GlobalParameter("deviceId", params.get("deviceId")));
		}
		if (params.containsKey("stationCategory")) {
			sql += " and t2.STATION_CATEGORY=@stationCategory";
			listParam.add(new GlobalParameter("stationCategory", params.get("stationCategory")));
		}
		List<RowBase> list = DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
		JSONObject json = DataUtil.listToJSONObject(list, null, pageInfo);
		return json;
	}
	/**
	 * 获取在线监测设备信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryMonitorEquipmentList(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,ms.name as SITE_NAME from BC_SITE_DEVICE_RPT t"
				+ " left join BC_SITE_INFO ms on t.MONITOR_POINT=ms.EXP_NO" + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.MONITOR_POINT=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 获取数采仪信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryDataCollectionInstrumentList(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,t2.name as SITE_NAME,t3.name as OPERATORNAME FROM BC_COLLECTION_INFO t "
				+ "	left join BC_SITE_INFO t2 on t.SITE_ID=t2.code "
				+ " left join OPERATIONS_UNIT t3 on t.operation_unit=t3.guid " + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.SITE_ID=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 根据污水处理厂id获取运营单位信息
	 */
	public List<RowBase> getOperationUnitByWWTPIDList(String wwtpId) {
		String sql = " select ou.* from BC_ENTERPRISE_ATTR wwtp " + "	left join OPERATIONS_UNIT ou ON ou.guid = wwtp.OPERATOR"
				+ " where wwtp.exp_no = @wwtp_id ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("wwtp_id", wwtpId));

		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	/**
	 * 获取运营单位
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase>  queryOperationUnitList(PageInfo pageInfo) throws Exception {
		String wwtp_id = RequestUnit.getString("WWTP_ID");
		String station_id = RequestUnit.getString("STATION_ID");
		
		Map<String,String> params = new HashMap<>();
		if(StringUtil.IsNotNullOrEmpty(wwtp_id)){
			params.put("wwtp_id", wwtp_id);
		}
		if(StringUtil.IsNotNullOrEmpty(station_id)){
			params.put("station_id", station_id);
		}
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("MONITOR_EQUIPMENT");		
		
		String sql = " select * from  OPERATIONS_UNIT where 1=1";
		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("GUID")) {
			sql += " and GUID=@GUID";
			listParam.add(new GlobalParameter("GUID", params.get("GUID")));
		}
		List<RowBase> list = DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
		return list;
	}
	
	public  Map<String,String> getStationQueryParams() {
		Map<String,String> params = new HashMap<>();
		String wwtp_id = RequestUnit.getParameter("WWTP_ID");
		String flow_type = RequestUnit.getParameter("FLOW_TYPE");
		String water_type = RequestUnit.getParameter("WATER_TYPE");
		String station_id = RequestUnit.getParameter("STATION_ID");
		String build_status = RequestUnit.getParameter("BUILD_STATUS");
		String station_name = RequestUnit.getParameter("STATION_NAME");
		String station_id_like = RequestUnit.getParameter("STATION_ID_LIKE");

		if (StringUtil.IsNotNullOrEmpty(wwtp_id)) {
			params.put("wwtp_id", wwtp_id);
		}
		if (StringUtil.IsNotNullOrEmpty(flow_type)) {
			params.put("flow_type", flow_type);
		}
		if (StringUtil.IsNotNullOrEmpty(water_type)) {
			params.put("water_type", water_type);
		}
		if (StringUtil.IsNotNullOrEmpty(station_id)) {
			params.put("station_id", station_id);
		}
		if (StringUtil.IsNotNullOrEmpty(build_status)) {
			params.put("build_status", build_status);
		}
		if(StringUtil.IsNotNullOrEmpty(station_name)){
			params.put("station_name", station_name);
		}
		if(StringUtil.IsNotNullOrEmpty(station_id_like)){
			params.put("station_id_like", station_id_like);
		}

		return params;
	}

	/**
	 * 更新站点信息，
	 * @throws Exception 
	 * 
	 */
	public void updateCompanyInfo() throws Exception {
		HashMap<String, Object> parms = RequestUnit.getHashMapParmO();
		String tableName = RequestUnit.getString("tableName");
		
		DataUtil.Update(tableName, parms);
	}
	/**
	 * 获取所有站点报警历史记录
	 * gislin
	 * 2016年5月7日
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception	
	 */
	public List<RowBase> StatisticsStationInfo(HashMap<String, String> params) throws Exception {
		
		String startStr = params.get("start") + " 00:00:00";
		String endStr = params.get("end") + " 23:59:59";
		
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date start = sf.parse(startStr);
		Date end = sf.parse(endStr);
		long time=end.getTime() - start.getTime();
		long time1 = Long.parseLong("7948800000");
		long totalMinute=0;
		if(time>86400000&&time<time1){//如果为false为按天统计
			totalMinute = (end.getTime() - start.getTime()-86400000) / 1000 / 60 + 1;
		}else if(time>time1){
			totalMinute = (end.getTime() - start.getTime()) / 1000 / 60 + 1;
		}
		else{
			totalMinute = (end.getTime() - start.getTime()) / 1000 / 60 + 1;
		}
				
		String sql=" select  t2.name,"
          +" nvl(sum(case when t1.type = '4' then t1.ARPCOUNT end),0) as NETWORK_DROP_TIMES, "   
          +" nvl(round(sum(t1.continue_time)/sum(t1.ARPCOUNT),0),0) as NETWORK_DROP_TIME_AVG, "  
          +" nvl(round("+totalMinute+" - sum(case when t1.type = '4' then t1.continue_time end),0),"+totalMinute+") as NETWORK_ONLINE_TIME  "
          +"  from BC_SITE_INFO t2 "
          +" left join operation_alarm_day_info t1 on t1.station_code=t2.code and t1.type = '4' ";
             if (params.containsKey("start")) {//开始时间
       			sql += " and t1.start_time >= to_date('" + startStr + "', 'YYYY-MM-DD HH24:MI:SS')";
       		}
       		if (params.containsKey("end")) {//结束时间
       			sql += " and t1.start_time <= to_date('" + endStr + "', 'YYYY-MM-DD HH24:MI:SS')";
       		}
       		
       		sql+=" where t2.station_category='3' and t2.build_status ='0' ";
       		if(params.containsKey("station_id")) {
       			sql += " and t2.code='"+params.get("station_id")+"'";
       		}
       		sql+=" group by t2.name order by NETWORK_ONLINE_TIME desc Nulls first ";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam,null);
	}
	/**
	 * 获取掉线站点
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> QueryAlarmInfo(String start,String end,String stationCode) throws Exception {
		
   			/**
   			 * 统计四种情况
   			 * 1. 在统计时间段内报警，并持续到统计时间段内结束的
   			 * 2. 在统计时间段内报警，并持续到统计时间段外的
   			 * 3. 在统计时间段外报警，并持续到统计时间段内的
   			 * 4. 在统计时间段外报警，并持续到统计时间段外的
   			 */
   			String sql = "select t1.station_code,\n" +
						"      t1.continue_time\n" + 
						" from OP_ALAR_INFO t1\n" + 
						"where t1.type = '4'\n" + 
						"  and t1.start_time >=\n" + 
						"      to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.updatetime <=\n" + 
						"      to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.station_code = '"+stationCode+"'\n" + 
   							"  union all\n" + 
						" select t1.station_code,\n" + 
						"       (to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS') - t1.start_time) * 24 * 60 continue_time\n" + 
						" from OP_ALAR_INFO t1\n" + 
						"where t1.type = '4'\n" + 
						"  and t1.start_time >=\n" + 
						"      to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.start_time <=\n" + 
						"      to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.updatetime >=\n" + 
						"      to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.station_code = '"+stationCode+"'\n" + 
   							"  union all\n" + 
						" select t1.station_code,\n" + 
						"       (t1.updatetime - to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 continue_time\n" + 
						" from OP_ALAR_INFO t1\n" + 
						"where t1.type = '4'\n" + 
						"  and t1.updatetime >=\n" + 
						"      to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.updatetime <=\n" + 
						"      to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.start_time <=\n" + 
						"      to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.station_code = '"+stationCode+"'\n" + 
   							"  union all\n" + 
						" select t1.station_code,\n" + 
						"        24 * 60 continue_time\n" + 
						" from OP_ALAR_INFO t1\n" + 
						"where t1.type = '4'\n" + 
						"  and t1.start_time <=\n" + 
						"      to_date('"+start+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.updatetime >=\n" + 
						"      to_date('"+end+"', 'YYYY-MM-DD HH24:MI:SS')\n" + 
						"  and t1.station_code = '"+stationCode+"'";

		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam,null);
	}
	
	/**
	 * 获取站点污染物
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public JSONObject queryDataExchangeInfoList() throws Exception {	
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("DATA_EXCHANGE");		
		PageInfo pageInfo = DataUtil.getPageInfo();
		String sql = " SELECT t1.* FROM DATA_EXCHANGE t1 ";
		List<RowBase> list = DataUtil.getPageRow(sql, null, pTable, pageInfo);
		JSONObject json = DataUtil.listToJSONObject(list, null, pageInfo);
		return json;
	}
	
	/**
	 * 根据污水厂id获取视频信息
	 * @param wwtpId
	 * @return
	 */
	public List<RowBase> getVideoDataByWWTPID(String wwtpId,String station_id) {
		String sql = " select t2.name,t1.* from BC_VIDEO_INFO t1 left join BC_SITE_INFO t2 on t1.station_code=t2.exp_no left join BC_ENTERPRISE_ATTR t3 on t2.wwtp_id=t3.exp_no "
				+ " where 1=1  ";
		List<GlobalParameter> listParam = new ArrayList<>();
       if( wwtpId != null && !wwtpId.equals("")){
    	   sql+=" and t3.exp_no = @wwtp_id";
    	   listParam.add(new GlobalParameter("wwtp_id", wwtpId));
       }
       if(station_id != null && !station_id.equals("")){
    	   sql+=" and t2.exp_no = @station_id";
    	   listParam.add(new GlobalParameter("station_id", station_id));
       }
		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	
	/**
	 * 根据站点id获取运营单位信息
	 */
	public List<RowBase> getOperationUnitByStationId(String stationId) {
		String sql = " select ou.* from BC_SITE_INFO t "
				+ "	left join OPERATIONS_UNIT ou ON ou.guid = t.OPERATIONSUNIT_ID" + " where t.exp_no = @station_id ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("station_id", stationId));

		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	
	/**
	 * 查询站点
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryStation(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		String sql = "select t.*,dc.status onoffstatus,dc.device_num,ri.river_basin_name,ri.river_name  from BC_SITE_INFO t\n"
				+ "       left join BC_COLLECTION_INFO dc on t.code = dc.site_id \n" 
				+ "       left join river_info ri on ri.river_code = t.river_code \n" 
				+ "       where 1=1 ";

		TwoTuple<String, List<GlobalParameter>> p = getStationQueryParams(params,false);
		String stationStatus = params.get("stationStatus");
		sql += p.first;
		sql += " order by dc.status desc nulls last,dc.site_id desc nulls last";
		List<RowBase> rows;
		if (pageInfo == null) {
			rows = DataUtil.getSelectRows(sql, p.second, pTable);
		} else {
			rows = DataUtil.getPageRow(sql, p.second, pTable, pageInfo);
		}
		
		List<RowBase> resultRows = new ArrayList<>();
		for(RowBase row : rows){
			String mn = row.getSafeString("DEVICE_NUM");
			String status = RedisHelper.getInstance().getStationStatus(mn);
			if(status == null || status.trim().equals("")){
				status = "3";
			}
			row.setValue("STATION_STATUS",status);
			if(stationStatus != null && stationStatus.equals(status)){
				resultRows.add(row);
			}
			else if(stationStatus == null){
				resultRows.add(row);
			}
		}
		return resultRows;
	}
	
	/**
	 * 获取数采仪信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getDataCollectionInstrument(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,t2.name as SITE_NAME,t3.name as OPERATORNAME FROM BC_COLLECTION_INFO t "
				+ "	left join BC_SITE_INFO t2 on t.SITE_ID=t2.code "
				+ " left join OPERATIONS_UNIT t3 on t.operation_unit=t3.guid " + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.SITE_ID=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 获取在线监测设备信息
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getMonitorEquipment(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " SELECT t.*,ms.name as SITE_NAME from BC_SITE_DEVICE_RPT t"
				+ " left join BC_SITE_INFO ms on t.MONITOR_POINT=ms.CODE" + " WHERE 1=1 ";

		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("wwtp_id")) {
			sql += " and t.WSCLC_ID=@wwtp_id";
			listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
		}
		if (params.containsKey("station_id")) {
			sql += " and t.MONITOR_POINT=@station_id";
			listParam.add(new GlobalParameter("station_id", params.get("station_id")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 根据污水厂id获取DVR信息
	 * @param wwtpId
	 * @return
	 */
	public List<RowBase> getDVRDataByWWTPID(String wwtpId,String station_id) {
		String sql = " select t2.name,t1.* from BC_DVR_INFO t1 left join BC_SITE_INFO t2 on t1.station_code=t2.exp_no left join BC_ENTERPRISE_ATTR t3 on t2.wwtp_id=t3.exp_no "
				+ " where 1=1  ";
		List<GlobalParameter> listParam = new ArrayList<>();
       if( wwtpId != null && !wwtpId.equals("")){
    	   sql+=" and t3.exp_no = @wwtp_id";
    	   listParam.add(new GlobalParameter("wwtp_id", wwtpId));
       }
       if(station_id != null && !station_id.equals("")){
    	   sql+=" and t2.exp_no = @station_id";
    	   listParam.add(new GlobalParameter("station_id", station_id));
       }
		List<RowBase> list = DataUtil.getSelectRows(sql, listParam, null);
		return list;
	}
	
	/**
	 * 获取运营单位
	 * 
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getOperationUnit(Map<String, String> params, ITable pTable, PageInfo pageInfo)
			throws Exception {
		String sql = " select * from  OPERATIONS_UNIT where 1=1";
		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("GUID")) {
			sql += " and GUID=@GUID";
			listParam.add(new GlobalParameter("GUID", params.get("GUID")));
		}
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
}
